﻿using System;
using System.Data;
using Gencode.Common.SQL;

namespace Gencode.PrsCommon.Data.Common
{
	public class CommonData
	{
		/// <summary>
		/// [name,tage,typwert,ATID]
		/// </summary>
		/// <param name="yearid"></param>
		/// <param name="addEmptyRow"></param>
		/// <returns>[name,tage,typwert,ATID]</returns>
		public static DataView GetMonths(int yearid, bool addEmptyRow)
		{
			DataView dv = SimpleQuerry.ExecuteForDataView("select Name,Tage,typwert,ATID from ArbeitsTage where typ = 1 and JahrID = " + yearid + " order by typwert");

			if (addEmptyRow)
			{
				DataRow row = dv.Table.NewRow();
				dv.Table.Rows.InsertAt(row, 0);
			}

			return dv;
		}

		/// <summary>
		/// Saves hours in a month
		/// </summary>
		/// <param name="yearid">yearid</param>
		/// <param name="monthid">typwert of month from ArbeitsTage</param>
		/// <param name="days">days to work in month</param>
		public static void SaveMonthDays(int yearid, int monthid, double days)
		{
			Querry stp = new Querry("upd_arbeitstage_tage");

			stp.AddParameter("@jahrid", yearid);
			stp.AddParameter("@typwert", monthid);
			stp.AddParameter("@tage", days);

			stp.Execute(true);
		}

		public static void CreateJear(int jahr, string von, string bis, string mandid)
		{
			Querry stp = new Querry("set_planungsjahr");

			stp.AddParameter("@jahr", jahr);
			stp.AddParameter("@mandid", mandid);
			stp.AddParameter("@von", von);
			stp.AddParameter("@bis", bis);

			stp.Execute(true);
		}

		/// <summary>
		/// Adds a mandant to the database
		/// </summary>
		/// <param name="mandid">shortcut/primary id for the mandant.</param>
		/// <param name="name">name of the mandant</param>
		/// <param name="description">description of the mandant</param>
		public static void AddMandant(string mandid, string name, string description)
		{
			Querry que = new Querry("set_mandant");

			que.AddParameter("@mandid", mandid);
			que.AddParameter("@name", name);
			que.AddParameter("@beschreibung", description);

			que.Execute(true);
		}

		/// <summary>
		/// Gets the mandid asociated with the user [mandid]
		/// </summary>
		/// <param name="user">userlogin</param>
		/// <returns>[mandid]</returns>
		public static string GetMandant(string user)
		{
			DataView dv = SimpleQuerry.ExecuteForDataView("select mandid from userrights where loginname = '" + user + "'");
			if (dv.Count == 0)
			{
				Gencode.Common.Util.ErrorHandler.LoggError("No Mandant", "", "There is no Mandant defined for the user: " + user);
				Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.USER_ERROR, "There is no Mandant defined for the user: " + user);
				return string.Empty;
			}
			return dv[0].Row[0].ToString();
		}

		/// <summary>
		/// adds a user to a mandant
		/// </summary>
		/// <param name="username"></param>
		/// <param name="loginname"></param>
		/// <param name="password"></param>
		/// <param name="beschreibung"></param>
		/// <param name="userrights"></param>
		/// <param name="mandid"></param>
		public static void AddUser(string username, string loginname, string password, string beschreibung,int userrights,string mandid)
		{
			Querry que = new Querry("set_userrights");

			que.AddParameter("@username", username);
			que.AddParameter("@loginname", loginname);
			que.AddParameter("@password", password);
			que.AddParameter("@beschreibung", beschreibung);
			que.AddParameter("@userrights", userrights);
			que.AddParameter("@mandid", mandid);

			que.Execute();
		}


		/// <summary>
		/// Checks and compares the database version with the api version.
		/// [-1 if db version smaller]
		/// [0 if ok]
		/// [1 if api version smaller]
		/// [-2 if no version]
		/// </summary>
		/// <param name="version"></param>
		/// <returns></returns>
		public static int CompareDatabaseVersion(Version version)
		{
			DataView dv = Gencode.Common.SQL.SimpleQuerry.ExecuteForDataView(
						"select Version from PrsVerwaltung where VerwaltungID = (select MAX(VerwaltungID) from PrsVerwaltung)");
			if(dv.Count<=0)
				return -2; //no version

			//string[] dbVers = dv[0].Row[0].ToString().Split('.');
			Version dbVersion = new Version(dv[0].Row[0].ToString());
			if (dbVersion > version)
				return 1;

			if (dbVersion < version)
				return -1;

			return 0;
		}

        /// <summary>
        /// Gets the version of the personalplaner database
        /// </summary>
        /// <returns></returns>
        public static string DatabaseVersion
        {
            get
            {
                DataView dv = Gencode.Common.SQL.SimpleQuerry.ExecuteForDataView(
                            "select Version from PrsVerwaltung where VerwaltungID = (select MAX(VerwaltungID) from PrsVerwaltung)");

                if (dv.Count <= 0)
                    return "0.0.0.0"; //no version

                return dv[0].Row[0].ToString();
            }
        }

		/// <summary>
		/// Creates a DataView with Months of active Year. JahrID,Jahr,Name,von,bis,ATID,Tage
		/// </summary>
		/// <returns>JahrID,Jahr,Name,von,bis,ATID,Tage</returns>
		public static DataView GetActiveMonths(string mandid)
		{
			Querry querry = new Querry();
			querry.SetSTPName("get_activemonths");
			querry.AddParameter("@mandid", mandid);

			return querry.ExecuteForDataView();
		}

		/// <summary>
		/// [days]
		/// </summary>
		/// <param name="atid"></param>
		/// <param name="mandid"></param>
		/// <returns></returns>
		public static int GetDaysInMonth(int atid, string mandid)
		{
			int retval=0;
			DataView dv = Gencode.Common.SQL.SimpleQuerry.ExecuteForDataView("select Tage from ArbeitsTage where ATID = " + atid + " and mandid = '" + mandid + "'");
			if (dv.Count > 0)
				try
				{
					retval = (int)double.Parse(dv[0].Row["Tage"].ToString());
				}
				catch { }
			
			dv.Dispose();

			return retval;
		}

	}
}
